﻿using lwzlz.Application.Information;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace lwzlz.Application.Model
{
    public class DataColumnDal
    {
        private static DataColumnDal _instance;
        private string conn = ConfigurationManager.ConnectionStrings["CMS"].ToString();
        //public static string conn = System.Web.HttpContext.Current.Session["sqlstring"].ToString();
        public static DataColumnDal GetInstance()
        {
            return _instance ?? (_instance = new DataColumnDal());
        }
        public void Edit(string db = "", string table = "", string colName = "", string value = "", string colId = "")
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.AppendFormat(" select count(*) AS Num from sys.extended_properties where minor_id='{0}' and major_id= ", colId);
            strSql.AppendFormat(" (select sys.tables.object_id from sys.tables where sys.tables.name='{0}') ", table);
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            connstring.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            bool tag = false;
            while (reader.Read())
            {
                tag = (int)reader["Num"] > 0;//如果tag为true则表明该注释已存在表中，只需要更新即可
            }
            connstring.Close();
            if (tag)
            {
                SqlConnection connstring1 = new SqlConnection(_instance.conn);
                connstring1.Open();
                var strSql1 = new StringBuilder(10);
                strSql1.AppendFormat("use {0};", dbSure);
                strSql1.AppendFormat("EXEC sp_updateextendedproperty 'MS_Description','{0}', 'user', dbo, 'table', '{1}', 'column', '{2}'", value, table, colName);
                SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), connstring);
                DataSet ds1 = null;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
                {
                    ds1 = new DataSet();
                    // Fill the DataSet using default values for DataTable names, etc
                    da.Fill(ds1);
                    connstring1.Close();
                }
            }
            else//需要插入
            {
                SqlConnection connstring1 = new SqlConnection(_instance.conn);
                connstring1.Open();
                var strSql1 = new StringBuilder(10);
                strSql1.AppendFormat("use {0};", dbSure);
                strSql1.AppendFormat("EXECUTE sp_addextendedproperty 'MS_Description','{0}', 'user', dbo, 'table', '{1}', 'column', '{2}'", value, table, colName);
                SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), connstring);
                DataSet ds1 = null;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
                {
                    ds1 = new DataSet();
                    // Fill the DataSet using default values for DataTable names, etc
                    da.Fill(ds1);
                    connstring1.Close();
                }
            }
        }
        public void EditTableName(string db = "", string table = "", string value = "")
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.AppendFormat(" select count(*) AS Num from sys.extended_properties where minor_id='{0}' and major_id= ", 0);
            strSql.AppendFormat(" (select sys.tables.object_id from sys.tables where sys.tables.name='{0}') ", table);
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            connstring.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            bool tag = false;
            while (reader.Read())
            {
                tag = (int)reader["Num"] > 0;//如果tag为true则表明该注释已存在表中，只需要更新即可
            }
            connstring.Close();
            if (tag)
            {
                SqlConnection connstring1 = new SqlConnection(_instance.conn);
                connstring1.Open();
                var strSql1 = new StringBuilder(10);
                strSql1.AppendFormat("use {0};", dbSure);
                strSql1.AppendFormat("EXEC sp_updateextendedproperty 'MS_Description','{0}', 'user', dbo, 'table', '{1}', null, null", value, table);
                SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), connstring);
                DataSet ds1 = null;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
                {
                    ds1 = new DataSet();
                    // Fill the DataSet using default values for DataTable names, etc
                    da.Fill(ds1);
                    connstring1.Close();
                }
            }
            else//需要插入
            {
                SqlConnection connstring1 = new SqlConnection(_instance.conn);
                connstring1.Open();
                var strSql1 = new StringBuilder(10);
                strSql1.AppendFormat("use {0};", dbSure);
                strSql1.AppendFormat("EXECUTE sp_addextendedproperty 'MS_Description','{0}', 'user', dbo, 'table', '{1}', null, null", value, table);
                SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), connstring);
                DataSet ds1 = null;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
                {
                    ds1 = new DataSet();
                    // Fill the DataSet using default values for DataTable names, etc
                    da.Fill(ds1);
                    connstring1.Close();
                }
            }
        }
        public void DeleteNull(string db = "", string colId = "", string table = "", string colName = "")
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.AppendFormat(" select count(*) AS Num from sys.extended_properties where minor_id='{0}' and major_id= ", colId);
            strSql.AppendFormat(" (select sys.tables.object_id from sys.tables where sys.tables.name='{0}') ", table);
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            connstring.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            bool tag = false;
            while (reader.Read())
            {
                tag = (int)reader["Num"] > 0;
            }
            connstring.Close();
            if (tag)
            {
                SqlConnection connstring1 = new SqlConnection(_instance.conn);
                connstring1.Open();
                var strSql1 = new StringBuilder(10);
                strSql1.AppendFormat("use {0};", dbSure);
                strSql1.AppendFormat("EXEC sp_dropextendedproperty 'MS_Description', 'user', dbo, 'table', '{0}', 'column', '{1}'", table, colName);
                SqlCommand cmd1 = new SqlCommand(strSql1.ToString(), connstring);
                DataSet ds1 = null;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd1))
                {
                    ds1 = new DataSet();
                    // Fill the DataSet using default values for DataTable names, etc
                    da.Fill(ds1);
                    connstring1.Close();
                }
            }

        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="db">数据库名称</param>
        /// <param name="table">表名</param>
        /// <returns></returns>
        public IList<DataColumnInfo> GetList(string db = "", string table = "", string rowName = "", string tag = "")
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.Append(" SELECT a.colid AS 'Id',a.name AS 'Name', ");
            strSql.Append(" d.name AS 'TableName', ");
            strSql.Append(" case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1' else '0' END AS 'IsIdentity', ");
            strSql.Append(" case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then IDENT_SEED(d.name) else null END AS 'IdentSeed', ");
            strSql.Append(" case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then IDENT_INCR(d.name) else null END AS 'IdentIncr', ");
            strSql.Append(" case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( ");
            strSql.Append(" SELECT name FROM sysindexes WHERE indid in( ");
            strSql.Append(" SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid)))  ");
            strSql.Append(" then '1' else '0' END AS 'IsKey', ");
            strSql.Append(" b.name AS 'Type',a.length AS 'Bytes', ");
            strSql.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 'Length', ");
            strSql.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 'Scale', ");
            strSql.Append(" case when a.isnullable=1 then '1' else '0' END AS 'IsNullable', ");
            strSql.Append(" isnull(e.text,'') AS 'DefaultValue',isnull(g.[value],'') AS Description ");
            strSql.Append(" FROM syscolumns a ");
            strSql.Append(" left join systypes b on a.xtype=b.xusertype ");
            strSql.Append(" inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
            strSql.Append(" left join syscomments e on a.cdefault=e.id ");
            strSql.Append(" left join sys.extended_properties g on d.id=g.major_id and a.colid=g.minor_id  ");
            strSql.AppendFormat(" where d.name='{0}' and (a.Name like '%{1}%' or cast(g.value as varchar(100)) like '%{2}%') ", table, rowName, rowName);
            if (tag != "")
            {
                strSql.Append(" order by a.id,a.colorder  ");
            }
            else
            {
                strSql.Append(" order by a.name ");
            }
            DataSet ds1 = null;
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds1 = new DataSet();
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds1);
                connstring.Close();
            }

            var list = new List<DataColumnInfo>();
            if (Utils.HasMoreRow(ds1))
            {
                list.AddRange(from DataRow dr in ds1.Tables[0].Rows select DataRowToModel(dr));
            }
            return list;
        }
        public IList<DataColumnInfo> GetAllList(string db = "", string rowName = "", string tag = "")
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.Append(" SELECT a.colorder AS 'Id',a.name AS 'Name', ");
            strSql.Append(" d.name AS 'TableName', ");
            strSql.Append(" case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1' else '0' END AS 'IsIdentity', ");
            strSql.Append(" case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then IDENT_SEED(d.name) else null END AS 'IdentSeed', ");
            strSql.Append(" case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then IDENT_INCR(d.name) else null END AS 'IdentIncr', ");
            strSql.Append(" case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in ( ");
            strSql.Append(" SELECT name FROM sysindexes WHERE indid in( ");
            strSql.Append(" SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid)))  ");
            strSql.Append(" then '1' else '0' END AS 'IsKey', ");
            strSql.Append(" b.name AS 'Type',a.length AS 'Bytes', ");
            strSql.Append(" COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 'Length', ");
            strSql.Append(" isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 'Scale', ");
            strSql.Append(" case when a.isnullable=1 then '1' else '0' END AS 'IsNullable', ");
            strSql.Append(" isnull(e.text,'') AS 'DefaultValue',isnull(g.[value],'') AS Description ");
            strSql.Append(" FROM syscolumns a ");
            strSql.Append(" left join systypes b on a.xtype=b.xusertype ");
            strSql.Append(" inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' ");
            strSql.Append(" left join syscomments e on a.cdefault=e.id ");
            strSql.Append(" left join sys.extended_properties g on d.id=g.major_id and a.colid=g.minor_id  ");
            strSql.Append(" where d.name in ");
            strSql.Append(" (select sys.tables.NAME ");
            strSql.Append(" from sys.tables) ");
            strSql.AppendFormat(" and a.Name like '%{0}%' or cast(g.value as varchar(100)) like '%{1}%' ", rowName, rowName);
            if (tag != "")
            {
                strSql.Append(" order by a.id,a.colorder  ");
            }
            else
            {
                strSql.Append(" order by TableName,a.name ");
            }
            DataSet ds1 = null;
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds1 = new DataSet();
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds1);
                connstring.Close();
            }

            var list = new List<DataColumnInfo>();
            if (Utils.HasMoreRow(ds1))
            {
                list.AddRange(from DataRow dr in ds1.Tables[0].Rows select DataRowToModel(dr));
            }
            return list;
        }
        public IList<TablesName> GetAllTables(string db = "")
        {
            string dbSure = "[" + db + "]";
            var strSql = new StringBuilder(10);
            strSql.AppendFormat("use {0};", dbSure);
            strSql.Append(" SELECT sys.tables.name as TableName from sys.tables ");
            DataSet ds1 = null;
            SqlConnection connstring = new SqlConnection(_instance.conn);
            SqlCommand cmd = new SqlCommand(strSql.ToString(), connstring);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                ds1 = new DataSet();
                // Fill the DataSet using default values for DataTable names, etc
                da.Fill(ds1);
                connstring.Close();
            }

            var list = new List<TablesName>();
            if (Utils.HasMoreRow(ds1))
            {
                list.AddRange(from DataRow dr in ds1.Tables[0].Rows select TablesNameToModel(dr));
            }
            return list;
        }
        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="row">数据行</param>
        /// <returns></returns>
        public DataColumnInfo DataRowToModel(DataRow row)
        {
            var model = new DataColumnInfo();
            if (row != null)
            {
                if (!string.IsNullOrEmpty(row["Id"].ToString()))
                {
                    model.Id = row["Id"].ToString();
                }
                if (!string.IsNullOrEmpty(row["TableName"].ToString()))
                {
                    model.TableName = row["TableName"].ToString();
                }
                if (!string.IsNullOrEmpty(row["Name"].ToString()))
                {
                    model.Name = row["Name"].ToString();
                }
                if (!string.IsNullOrEmpty(row["IsIdentity"].ToString()))
                {
                    model.IsIdentity = row["IsIdentity"].ToString();
                    model.IsIdentityDesc = model.IsIdentity == "0" ? "NO" : "Yes";
                }
                if (!string.IsNullOrEmpty(row["IdentSeed"].ToString()))
                {
                    model.IdentSeed = row["IdentSeed"].ToString();
                }
                if (!string.IsNullOrEmpty(row["IdentIncr"].ToString()))
                {
                    model.IdentIncr = row["IdentIncr"].ToString();
                }
                if (!string.IsNullOrEmpty(row["IsKey"].ToString()))
                {
                    model.IsKey = row["IsKey"].ToString();
                    model.IsKeyDesc = model.IsKey == "0" ? "NO" : "Yes";
                }
                if (!string.IsNullOrEmpty(row["Type"].ToString()))
                {
                    model.Type = row["Type"].ToString();
                }
                if (!string.IsNullOrEmpty(row["Bytes"].ToString()))
                {
                    model.Bytes = row["Bytes"].ToString();
                }
                if (!string.IsNullOrEmpty(row["Length"].ToString()))
                {
                    model.Length = row["Length"].ToString();
                }
                if (!string.IsNullOrEmpty(row["Scale"].ToString()))
                {
                    model.Scale = row["Scale"].ToString();
                }
                if (!string.IsNullOrEmpty(row["IsNullable"].ToString()))
                {
                    model.IsNullable = row["IsNullable"].ToString();
                    model.IsNullableDesc = model.IsNullable == "0" ? "NO" : "Yes";
                }
                if (!string.IsNullOrEmpty(row["DefaultValue"].ToString()))
                {
                    model.DefaultValue = row["DefaultValue"].ToString();
                }
                if (!string.IsNullOrEmpty(row["Description"].ToString()))
                {
                    model.Description = row["Description"].ToString();
                }
            }
            return model;
        }
        public TablesName TablesNameToModel(DataRow row)
        {
            var model = new TablesName();
            if (row != null)
            {
                if (!string.IsNullOrEmpty(row["TableName"].ToString()))
                {
                    model.TableName = row["TableName"].ToString();
                }
            }
            return model;
        }
    }
}